import pandas as pd
import matplotlib.pyplot as plt
import matplotlib

# 设置中文字体
matplotlib.rcParams['font.sans-serif'] = ['SimHei']
matplotlib.rcParams['axes.unicode_minus'] = False

# 读取Excel文件
data = pd.read_excel('d:\\2025实训\\minda-practical-training\\project\\练习一\\FhjlViewDD.xlsx')

# 将日期列转换为datetime格式
data['创建时间'] = pd.to_datetime(data['创建时间'])

# 筛选6月份数据
june_data = data[data['创建时间'].dt.month == 6]

# 按客户分组求和并排序
customer_sum = june_data.groupby('客户名称')['净重'].sum().sort_values(ascending=False)

# 按日期分组求和
daily_sum = june_data.groupby(june_data['创建时间'].dt.day)['净重'].sum().sort_index()

# 绘制客户货运量统计图
plt.figure(figsize=(16, 8))
plt.bar(customer_sum.index, customer_sum.values, color='blue', edgecolor='black', alpha=0.7)
plt.title('6月份各客户矿粉货运量统计', fontsize=18, pad=20)
plt.xlabel('客户名称', fontsize=15)
plt.ylabel('货运量(吨)', fontsize=15)
plt.grid(axis='y', linestyle='--', alpha=0.5)
plt.xticks(rotation=45, ha='right')

# 添加数值标签
for i, v in enumerate(customer_sum.values):
    plt.text(i, v+0.5, f'{int(v):,}', 
             ha='center', fontsize=10, 
             bbox=dict(facecolor='white', alpha=0.8, edgecolor='none'))

# 保存客户统计图表
plt.savefig('d:\\2025实训\\minda-practical-training\\project\\customer_mineral_powder_june.png', 
            bbox_inches='tight', dpi=300, facecolor='white')
plt.close()

# 绘制每日货运量统计图
plt.figure(figsize=(16, 8))
plt.bar(daily_sum.index, daily_sum.values, color='blue', edgecolor='black', alpha=0.7)
plt.title('6月份每日矿粉货运量统计', fontsize=18, pad=20)
plt.xlabel('日期 (6月)', fontsize=15)
plt.ylabel('货运量(吨)', fontsize=15)
plt.grid(axis='y', linestyle='--', alpha=0.5)
plt.xticks(daily_sum.index, [f'6月{int(i)}日' for i in daily_sum.index], rotation=45, ha='right')

# 添加交互式数值标签
for i, v in enumerate(daily_sum.values):
    plt.text(i+1, v+0.5, f'{int(v):,}', 
             ha='center', fontsize=10, 
             bbox=dict(facecolor='white', alpha=0.8, edgecolor='none'))
    
# 添加平均线
avg_value = daily_sum.values.mean()
plt.axhline(y=avg_value, color='red', linestyle='--', linewidth=1.5, alpha=0.7)
plt.text(len(daily_sum)+0.5, avg_value, f'平均: {avg_value:,.1f}', 
         va='center', ha='right', color='red', fontsize=12)

# 保存每日统计图表
plt.savefig('d:\\2025实训\\minda-practical-training\\project\\mineral_powder_june.png', 
            bbox_inches='tight', dpi=300, facecolor='white')
plt.show()